<?php
/**
 * 
 * @author Michale yeh zhanye115@gmail.com
 *
 */
class Category_model extends CI_Model {
	/**
	 * 构造函数
	 */
	function __construct()
    {
        parent::__construct();
    }
	/**
	 * 获取单个分类
	 * @param int $id
	 */
	public function find_category_by_id($id) {
		$this->db->where('id', intval($id));
		return $this->db->get('categories')->row_array();
	}
	
	/**
	 * 插入子节点
	 * @param string $name
	 * @param int $parent_id 父id
	 */
	public function insert_child_category($name, $parent_id) {
		$sql = "select lft from categories where id=" . intval($parent_id);
		$parent_node = $this->db->query($sql)->row_array();
		$lft = empty($parent_node['lft']) ? 0 : intval($parent_node['lft']);
		$sql = "update categories set rgt = rgt + 2 where rgt > " . $lft;
		$this->db->query($sql);
		$sql = "update categories set lft = lft + 2 where lft > " . $lft;
		$this->db->query($sql);
		$sql = "insert into categories set name=" . $this->db->escape($name) . ",lft=" . ($lft+1) .",rgt=" .
		($lft+2) . ",parent_id=" . $parent_id;
		$this->db->query($sql);
		return $this->db->insert_id();
	}
	/**
	 * 插入兄弟节点
	 * @param string name
	 * @param int $id
	 */
	public function insert_sibling_category($name, $id) {
		$sql = "select rgt,parent_id from categories where id=" . intval($id);
		$sibling_node = $this->db->query($sql)->row_array();
		$rgt = empty($sibling_node['rgt']) ? 0 : intval($sibling_node['rgt']);
		$sql = "update categories set rgt = rgt + 2 where rgt > " . $rgt;
		$this->db->query($sql);
		$sql = "update categories set lft = lft + 2 where lft > " . $rgt;
		$this->db->query($sql);
		$sql = "insert into categories set name=" . $this->db->escape($name) . ",lft=" . ($rgt+1) .",rgt=" .
		($rgt+2) . ",parent_id=" . $sibling_node['parent_id'];
		$this->db->query($sql);
		return 1;
	}
	
	/**
	 * 删除节点，不删除其子节点,子节点的父节点上移
	 * @param int $id
	 */
	public function delete_category_without_child($id) {
		$node = $this->find_category_by_id($id);
		if (empty($node)) {
			return ;
		}
		$lft = $node['lft'];
		$rgt = $node['rgt'];
		
		$sql = "delete from categories where lft=" . intval($lft);
		$this->db->query($sql);
		
		$sql = "update categories set rgt=rgt-1,lft=lft-1, parent_id=" . $node['parent_id'] . " where lft between " . $lft . " and " .$rgt;
		$this->db->query($sql);
		
		$sql = "update categories set rgt = rgt -2 where rgt > " . $rgt;
		$this->db->query($sql);
		$sql = "update categories set lft = lft - 2 where lft > " . $rgt;
		$this->db->query($sql);
		return 1;	
	}
	
	/**
	 * 删除节点，删除其子节点
	 * @param int $id
	 */
	public function delete_category_with_child($id) {
		$node = $this->find_category_by_id($id);
		if (empty($node)) {
			return ;
		}
		$lft = $node['lft'];
		$rgt = $node['rgt'];
		$width = $rgt - $lft + 1;
		
		
		$sql = "delete from  categories where lft between " . $lft . " and " .$rgt;
		$this->db->query($sql);
		
		$sql = "update categories set rgt = rgt - " . $width . " where rgt > " . $rgt;
		$this->db->query($sql);
		$sql = "update categories set lft = lft - " . $width . " where lft > " . $rgt;
		$this->db->query($sql);
		return 1;	
	}
	/**
	 * 查找所有叶子子类
	 */
	public function find_leaf_category() {
		$sql = "select id, name from categories where rgt = lft + 1";
		return $this->db->query($sql)->result_array();
	}
	/**
	 * 获取分类树
	 */
	public function find_all_category() {
		$sql = "SELECT node.id, node.name, (COUNT(parent.id) - 1) AS depth,node.parent_id 
			FROM categories AS node,
			categories AS parent
			WHERE node.lft BETWEEN parent.lft AND parent.rgt
			GROUP BY node.id
			ORDER BY node.lft;
		";
		return $this->db->query($sql)->result_array();
	}
	/**
	 * 获取直接子分类
	 */
	public function find_son_category($id) {
		$sql = "SELECT * from categories where parent_id=" . intval($id) . " order by ordering asc";
		return $this->db->query($sql)->result_array();
	}
	/**
	 * 获取父类
	 * @param int $id
	 */
	public function find_father_category($id) {
		$sql = "SELECT parent.id FROM categories AS node,categories AS parent
				where node.id = " . $id . " and parent.lft<node.lft and parent.rgt>node.rgt 
				order by parent.lft desc limit 1 ";
		$result = $this->db->query($sql)->row_array();
		return $result['id'];
	}
	/**
	 * 获取所有子分类
	 */
	public function find_all_child_category($id) {
		$sql = "SELECT node.*,(COUNT(parent.id) - (sub_tree.depth + 1)) AS depth
					FROM categories AS node,
						categories AS parent,
						categories AS sub_parent,
						(
							SELECT node.id, (COUNT(parent.id) - 1) AS depth
							FROM categories AS node,
							categories AS parent
							WHERE node.lft BETWEEN parent.lft AND parent.rgt
							AND node.id = " . $id . "
							GROUP BY node.id
							ORDER BY node.lft
						)AS sub_tree
					WHERE node.lft BETWEEN parent.lft AND parent.rgt
						AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
						AND sub_parent.id = sub_tree.id
					GROUP BY node.id having depth>0
					ORDER BY node.ordering asc ,node.id asc;
		";
		
		$results = $this->db->query($sql)->result_array();
		$return_array = array();
		foreach ($results as $row) {
			if (!isset($return_array[$row['depth']])) {
				$return_array[$row['depth']] = array();
			}
			$return_array[$row['depth']][] = $row;
		}
		return $return_array;
	}
	/**
	 * 返回一级目录的所有子三级目录
	 * @param int $id
	 */
	public function find_grandson_category($id) {
		$sql = "SELECT node.name, node.id,node.parent_id,(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
					FROM categories AS node,
						categories AS parent,
						categories AS sub_parent,
						(
							SELECT node.name, (COUNT(parent.name) - 1) AS depth
							FROM categories AS node,
							categories AS parent
							WHERE node.lft BETWEEN parent.lft AND parent.rgt
							AND node.id = " . $id . "
							GROUP BY node.name
							ORDER BY node.lft
						)AS sub_tree
					WHERE node.lft BETWEEN parent.lft AND parent.rgt
						AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
						AND sub_parent.name = sub_tree.name
					GROUP BY node.name having depth=2
					ORDER BY node.lft;
		";
		
		$results = $this->db->query($sql)->result_array();
		return $results;
	}
	/**
	 * 更新分类，名称和ordering
	 * @param int $id
	 * @param array $data
	 */
	public function update_category($id, $data) {        
		$this->db->where('id', $id);
		$this->db->update('categories', $data); 
	}
	/**
	 * 更新分类的ordering
	 * @param int $id
	 * @param int $ordering
	 */
	public function set_ordering($id, $ordering) {
		$this->db->where('id', $id);
		$this->db->update('categories', array('ordering' => intval($ordering))); 
	}
	
}